﻿USE [ParieurProDB]
GO

/****** Object:  StoredProcedure [dbo].[mybet_createCustomContest]    Script Date: 03/01/2011 22:40:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Franck Allion
-- Create date: 22/04/12
-- Description:	User create new custom contest
-- =============================================
CREATE PROCEDURE [dbo].[mybet_createCustomContest]
	@UserId             uniqueidentifier,
	@ContestId   int,
	@DisplayName nvarchar(256),
	@Password nvarchar(512)
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
	DECLARE @CustomContestId int
	SELECT @CustomContestId = 0

    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
		
    -- Insert new customcontest    
	INSERT CustomContest (AdministratorId, DisplayName, Password, IsDefault)
	VALUES (@UserId,@DisplayName,@Password,0);
	
	IF( @@ERROR <> 0 )
    BEGIN
		-- Impossible d'insérer le tournoi
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
	SET @CustomContestId = @@IDENTITY

    -- Insert link betweek CustomContest and Contest
	INSERT CustomContestLinks (CustomContestId, ContestId)
	VALUES (@CustomContestId,@ContestId);
	
	IF( @@ERROR <> 0 )
    BEGIN
		-- Impossible d'insérer le tournoi
		SET @ErrorCode = -3
		GOTO Cleanup
    END

	-- Insert administrator subscription
    INSERT Subscriptions (UserId, CustomContestId)
	VALUES (@UserId,@CustomContestId);
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -4
		GOTO Cleanup
    END
    
    INSERT Ranks (EventCount, GoodPredictionCount, GoodScoreCount,
    MatchCount, SubscriptionId, TotalScore, CurrentRank)
    VALUES (0,0,0,0,@@IDENTITY,0,0); 
  	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -5
		GOTO Cleanup
    END  

    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO


USE [ParieurProDB]
GO

/****** Object:  StoredProcedure [dbo].[mybet_deleteCustomContest]    Script Date: 06/24/2012 11:34:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Franck Allion
-- Create date: 22/04/12
-- Description:	User create new custom contest
-- =============================================
CREATE PROCEDURE [dbo].[mybet_deleteCustomContest]
	@UserId             uniqueidentifier,
	@CustomContestId   int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    ELSE
		BEGIN
			SET @ErrorCode = -1
    		GOTO Cleanup
		END
	
	-- Suppression des classements
	DELETE Ranks WHERE SubscriptionId IN 
	(SELECT SubscriptionId FROM Subscriptions WHERE CustomContestId=@CustomContestId);
	
	-- Suppression des abonnements
	DELETE Subscriptions WHERE CustomContestId=@CustomContestId;
	
	-- Impossible de supprimer un tournoi s'il y a des abonnés
	DELETE CustomContestLinks WHERE CustomContestId=@CustomContestId;
	
    -- Delete customcontest    
	DELETE CustomContest WHERE AdministratorId=@UserId AND CustomContestId=@CustomContestId;

	IF( @@ERROR <> 0 )
    BEGIN
		-- Impossible de supprimer le tournoi
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO

-- tournoi par défaut d'un utilisateur
ALTER TABLE Subscriptions
ADD [IsDefault] tinyint NULL;
GO


/****** Object:  StoredProcedure [dbo].[mybet_joinContest]    Script Date: 06/24/2012 17:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Franck Allion
-- Create date: 26/02/11
-- Description:	User join a contest
-- Output :
--  Erreurs fonctionnelles
--	-8 : Utilisateur déjà inscrit
--  -7 : Utilisateur non autorisé sur le tournoi
--  -9 : le tournoi n'existe pas
--  Erreurs SQL
--  -2 : Erreur à l'inscription
--  -3 : Erreur à la création de l'entrée dans les scores
--  -4 : Erreur à la suppression des rôles
--  -5 : Le rôle "Player" n'existe pas
--  -6 : Erreur lors de l'affectation du rôle "Player"
-- =============================================
ALTER PROCEDURE [dbo].[mybet_joinContest]
	@UserId             uniqueidentifier,
	@CustomContestName		nvarchar(256),
	@CustomContestPassword	nvarchar(512)
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    DECLARE @DefaultContestId int
    SET @DefaultContestId = 0
    
    DECLARE @CurrentContestId int
    SET @CurrentContestId = 0
    
	DECLARE @CustomContestId int
    SET @CustomContestId = 0

	DECLARE @RoleCount int
    SET @RoleCount = 0

	DECLARE @DefaultSubscriptionCount int
    SET @DefaultSubscriptionCount = 0

	DECLARE @DefaultContest int
    SET @DefaultContest = 0

    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    
	-- Récupération de l'identifiant du tournoi
	SELECT @CustomContestId=CustomContestId FROM CustomContest WHERE UPPER(DisplayName)=UPPER(@CustomContestName) AND Password=@CustomContestPassword;
	IF ( @CustomContestId = 0 )
	BEGIN
		-- Tournoi inconnu ou mot de passe incorrect
		SET @ErrorCode = -9
		GOTO Cleanup
	END

    -- Vérification que l'utilisateur n'est pas déjà inscrit
    SELECT @CurrentContestId=COUNT(Subscriptions.CustomContestId) FROM Subscriptions WHERE
    UserId=@UserId AND CustomContestId=@CustomContestId;
    IF ( @CurrentContestId = 1 )
    BEGIN
		-- Déjà inscrit
		SET @ErrorCode = -8
		GOTO Cleanup
    END
    			
Subscription:
	
	SELECT @DefaultSubscriptionCount=COUNT(Subscriptions.CustomContestId) FROM Subscriptions 
	WHERE UserId=@UserId AND IsDefault=1;
	
	IF ( @CurrentContestId = 0 )
	BEGIN
		SET @DefaultContest=1
	END
		
    -- Insert new subscription
    DELETE Subscriptions 
		WHERE UserId=@UserId AND CustomContestId=@CustomContestId;
    
	INSERT Subscriptions (UserId, CustomContestId, IsDefault)
	VALUES (@UserId,@CustomContestId,@DefaultContest);
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
    INSERT Ranks (EventCount, GoodPredictionCount, GoodScoreCount,
    MatchCount, SubscriptionId, TotalScore, CurrentRank)
    VALUES (0,0,0,0,@@IDENTITY,0,0); 
    
  	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -3
		GOTO Cleanup
    END  
    
	SELECT @RoleCount=COUNT(UserId) FROM aspnet_UsersInRoles WHERE
    UserId=@UserId AND RoleId=(SELECT RoleId from aspnet_roles where LoweredRoleName='registered users');
    IF ( @RoleCount = 1 )
    BEGIN
		-- Delete user role
		DELETE aspnet_UsersInRoles 
			WHERE UserId=@UserId AND RoleId=(SELECT RoleId from aspnet_roles where LoweredRoleName='registered users');
		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -4
			GOTO Cleanup
		END		

		-- Give player role
		-- Get Player role id
		SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE RoleName = 'Player'
		IF ( @RoleId IS NULL )
		BEGIN
 			SET @ErrorCode = -5
			GOTO Cleanup       
		END

		-- Add role	
		INSERT INTO aspnet_UsersInRoles  
		( UserId, 
		  RoleId )
		VALUES
		( @UserId, @RoleId)
	
		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -6
			GOTO Cleanup
		END
	END

    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END


/****** Object:  StoredProcedure [dbo].[mybet_createCustomContest]    Script Date: 03/01/2011 22:40:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Franck Allion
-- Create date: 22/04/12
-- Description:	User create new custom contest
-- =============================================
CREATE PROCEDURE [dbo].[mybet_setDefaultCustomContest]
	@UserId             uniqueidentifier,
	@CustomContestId   int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
		
    -- RAZ des tournois   
	UPDATE Subscriptions Set IsDefault=0 WHERE UserId=@UserId;
	IF( @@ERROR <> 0 )
    BEGIN
		-- Impossible de faire le RAZ
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
	-- Fixe le tournoi par défaut   
	UPDATE Subscriptions Set IsDefault=1 WHERE UserId=@UserId AND CustomContestId=@CustomContestId;
	IF( @@ERROR <> 0 )
    BEGIN
		-- Impossible de faire le RAZ
		SET @ErrorCode = -3
		GOTO Cleanup
    END
  
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO

/****** Object:  Table [dbo].[CustomContestEventScores]    Script Date: 07/25/2012 14:53:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomContestEventScores](
	[UserId] [uniqueidentifier] NOT NULL,
	[EventId] [int] NOT NULL,
	[IsBestScore] [tinyint] NULL,
	[CustomContestId] [int] NOT NULL,
	[CurrentRank] [int] NULL,
	[IsCompleted] [tinyint] NULL,
 CONSTRAINT [PK_CustomContestEventScores] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[EventId] ASC,
	[CustomContestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CustomContestEventScores]  WITH CHECK ADD  CONSTRAINT [FK_CustomContestEventScores_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[CustomContestEventScores] CHECK CONSTRAINT [FK_CustomContestEventScores_aspnet_Users]
GO

ALTER TABLE [dbo].[CustomContestEventScores]  WITH CHECK ADD  CONSTRAINT [FK_CustomContestEventScores_CustomContest] FOREIGN KEY([CustomContestId])
REFERENCES [dbo].[CustomContest] ([CustomContestId])
GO

ALTER TABLE [dbo].[CustomContestEventScores] CHECK CONSTRAINT [FK_CustomContestEventScores_CustomContest]
GO

ALTER TABLE [dbo].[CustomContestEventScores]  WITH CHECK ADD  CONSTRAINT [FK_CustomContestEventScores_CustomContestEventScores] FOREIGN KEY([UserId], [EventId], [CustomContestId])
REFERENCES [dbo].[CustomContestEventScores] ([UserId], [EventId], [CustomContestId])
GO

ALTER TABLE [dbo].[CustomContestEventScores] CHECK CONSTRAINT [FK_CustomContestEventScores_CustomContestEventScores]
GO

ALTER TABLE [dbo].[CustomContestEventScores]  WITH CHECK ADD  CONSTRAINT [FK_CustomContestEventScores_Events] FOREIGN KEY([EventId])
REFERENCES [dbo].[Events] ([EventId])
GO

ALTER TABLE [dbo].[CustomContestEventScores] CHECK CONSTRAINT [FK_CustomContestEventScores_Events]
GO





-- =============================================
-- Author:		Franck Allion
-- Create date: 22/04/12
-- Description:	Update score for a user
-- =============================================
CREATE PROCEDURE [dbo].[mybet_updateScore]
	@UserId    uniqueidentifier,
	@EventId   int,
	@Score int,
	@GoodPredictionCount int,
	@GoodScoreCount int,
	@CurrentRank int,
	@IsBestScore tinyint,
	@IsCompleted tinyint,
	@CustomContestId int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
	DECLARE @UserCount int
	SET @UserCount = 0


    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
		
    -- Mise à jour / insertion   
	SELECT @UserCount=COUNT(UserId) FROM EventScores WHERE UserId=@UserId AND EventId=@EventId;
    IF ( @UserCount = 1 )
	BEGIN
		-- Mise à jour
		UPDATE EventScores SET Score=@Score, GoodPredictionCount=@GoodPredictionCount, GoodScoreCount=@GoodScoreCount, IsCompleted=@IsCompleted 
			WHERE UserId=@UserId AND EventId=@EventId;
		
		IF( @@ERROR <> 0 )
		BEGIN
			-- Impossible de faire la mise à jour
			SET @ErrorCode = -1
			Goto Cleanup
		END
	END
	ELSE
	BEGIN
		INSERT INTO EventScores(UserId,EventId,Score,GoodPredictionCount,GoodScoreCount,IsCompleted) 
			VALUES (@UserId,@EventId,@Score,@GoodPredictionCount,@GoodScoreCount,@IsCompleted);
		IF( @@ERROR <> 0 )
		BEGIN
			-- Impossible de faire l'insertion
			SET @ErrorCode = -2
			Goto Cleanup
		END
	END

	-- Mise à jour ou insertion 
	SELECT @UserCount=COUNT(UserId) FROM CustomContestEventScores WHERE UserId=@UserId AND EventId=@EventId AND CustomContestId=@CustomContestId;
    IF ( @UserCount = 1 )
	BEGIN
		-- Mise à jour
		UPDATE CustomContestEventScores SET IsBestScore=@IsBestScore,CurrentRank=@CurrentRank,IsCompleted=@IsCompleted WHERE UserId=@UserId AND EventId=@EventId AND CustomContestId=@CustomContestId;
		IF( @@ERROR <> 0 )
		BEGIN
			-- Impossible de faire la mise à jour
			SET @ErrorCode = -3
			Goto Cleanup
		END
	END
	ELSE
	BEGIN
		INSERT INTO CustomContestEventScores (UserId,EventId,CustomContestId,IsBestScore,CurrentRank,IsCompleted) VALUES
		(@UserId,@EventId,@CustomContestId,@IsBestScore,@CurrentRank,@IsCompleted);
		IF( @@ERROR <> 0 )
		BEGIN
			-- Impossible de faire l'insertion
			SET @ErrorCode = -4
			Goto Cleanup
		END
	END
  
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO


-- Nouvelles équipes

INSERT [dbo].[Teams] (Name,ShortName) VALUES ('Stade de Reims','Reims');
INSERT [dbo].[Teams] (Name,ShortName) VALUES ('SC Bastia','Bastia');
INSERT [dbo].[Teams] (Name,ShortName) VALUES ('ESTAC Troyes','Troyes');

DECLARE @img VARBINARY(MAX)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\Users\fal\Documents\Visual Studio 2010\Projects\MyBet\my-phoenix-project\MyBet.Web\images\Logo\bastia.png', SINGLE_BLOB) AS x

update [ParieurProDB].dbo.Teams Set [ParieurProDB].dbo.Teams.Icon=@img
where [ParieurProDB].dbo.Teams.Name='SC Bastia';

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\Users\fal\Documents\Visual Studio 2010\Projects\MyBet\my-phoenix-project\MyBet.Web\images\Logo\reims.png', SINGLE_BLOB) AS x

update [ParieurProDB].dbo.Teams Set [ParieurProDB].dbo.Teams.Icon=@img
where [ParieurProDB].dbo.Teams.Name='Stade de Reims';

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\Users\fal\Documents\Visual Studio 2010\Projects\MyBet\my-phoenix-project\MyBet.Web\images\Logo\estac.png', SINGLE_BLOB) AS x

update [ParieurProDB].dbo.Teams Set [ParieurProDB].dbo.Teams.Icon=@img
where [ParieurProDB].dbo.Teams.Name='ESTAC Troyes';

-- Modification des abonnements

INSERT [dbo].[Seasons] (StartYear,EndYear,Display)
VALUES (2012,2013,'2012-2013');

UPDATE [dbo].[Contests] SET IsClosed=1 WHERE ChampionShipId=1 AND SeasonId=1;

INSERT [dbo].[Contests] (ChampionShipId,SeasonId,IsClosed) VALUES
(1,2,0);

-- suppression de la contraine unique sur IX_UserIdUnique
DROP INDEX Subscriptions.IX_UserIdUnique;

-- change password length
ALTER TABLE CustomContest ALTER COLUMN Password NVARCHAR(512);

-- Set default contests
UPDATE Subscriptions SET IsDefault=1 WHERE CustomContestId=1;

insert CustomContestEventScores (UserId,EventId,IsBestScore,CustomContestId,CurrentRank,IsCompleted)
select UserId,EventId,IsBestScore,1,CurrentRank,IsCompleted from EventScores;